﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CRMFramework;
using System.Collections;
using Newtonsoft.Json;
using CRMEntity;
using System.Data.Common;

namespace CRMBiz
{
    public static class SecurityHandler
    {
        public static DataSet GetSecurityofLoans(int pageIndex, int pageSize, int LoanID)
        {
            DataSet ds;
            try
            {
                var para = new[]{
                    DBhelper.SetParameter("@LoanID",LoanID)
                };
                var sbSql = new StringBuilder("SELECT c.*,(select count(*) from crm_vehicles where app_id in (select app_id from am_loan1 where loanid=@LoanID)) as Total,(select SUM(CAST(replace(ifnull(app_VehicleValue,'0'),',','') AS DECIMAL(10,2))) from crm_vehicles where app_VehicleValue!='' and app_id in (select app_id from am_loan1 where loanid=@LoanID)) as SumVehicleValue,(select SUM(CAST(replace(ifnull(app_VehiclePayment,'0'),',','') AS DECIMAL(10,2))) from crm_vehicles where app_VehiclePayment!='' and app_id in (select app_id from am_loan1 where loanid=@LoanID)) as SumVehiclePayment FROM crm_vehicles c where app_id in (select app_id from am_loan1 where loanid=@LoanID) order by app_VehicleId");
                sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                ds = DBhelper.ExecuteDataset(sbSql.ToString(), para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static DataSet GetRealProperty(int pageIndex, int pageSize, int LoanID)
        {
            DataSet ds;
            try
            {
                var para = new[]{
                    DBhelper.SetParameter("@LoanID",LoanID)
                };
                var sbSql = new StringBuilder("SELECT *,(select count(*) from crm_realestate where app_id=(select app_id from am_loan1 where loanid=@LoanID)) as Total,(select SUM(CAST(replace(ifnull(app_PropertyValue,'0'),',','') AS DECIMAL(10,2))) from crm_realestate where app_PropertyValue!='' and app_id=(select app_id from am_loan1 where loanid=@LoanID)) as SumPropertyValue,(select SUM(CAST(replace(ifnull(app_PropertyOwing,'0'),',','') AS DECIMAL(10,2))) from crm_realestate where app_PropertyOwing!='' and app_id=(select app_id from am_loan1 where loanid=@LoanID)) as SumPropertyOwing FROM crm_realestate where app_id=(select app_id from am_loan1 where loanid=@LoanID) order by app_PropertyId");
                sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                ds = DBhelper.ExecuteDataset(sbSql.ToString(), para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static DataSet GetSecurityForLoan(int pageIndex, int pageSize, int LoanID)
        {
            DataSet ds;
            try
            {
                var para = new[]{
                    DBhelper.SetParameter("@LoanID",LoanID)
                };
                //var sbSql = new StringBuilder(" Select SecurityVehicleId as id,'Vehicle' as 'SecurityType',CAST(CONCAT(ifnull(VehicleYear,''),' ',ifnull(VehicleMake,''),' ' ,ifnull(VehicleModel,'')) AS char(100)) as 'Security',VehicleValue as 'Value',VehicleOwning as 'Owning' , RegisteredOwner as 'Owner',VehicleSecurityStatus as 'Status' from am_securityVehicle where LoanID=@LoanID")
                //   .Append(" UNION Select SecurityPropertyID as id,'Property' as 'SecurityType',ifnull(Address,'') as 'Security',PropertyValue as 'Value',PropertyOwning as 'Owning' , PropertyOwner as 'Owner',PropertySecurityStatus as 'Status' from am_securityProperty where LoanID=@LoanID")
                //   .Append(" UNION Select companyid as id,'Company' as 'SecurityType',ifnull(CompanySecurity,'') as 'Security',CAST(('0.00') as DECIMAL(10,2)) as 'Value',CAST(('0.00') as DECIMAL(10,2)) as 'Owning' , CompanyName as 'Owner',SecurityStatus as 'Status' from am_securitycompany where LoanID=@LoanID");

                var sbSql = new StringBuilder(" Select VehicleID as id,'Vehicle' as 'SecurityType',CAST(CONCAT(ifnull(VehicleYear,''),' ',ifnull(Make,''),' ' ,ifnull(Model,'')) AS char(100)) as 'Security',VehicleValue as 'Value',VehicleOwing as 'Owning' , Owner as 'Owner',SecurityStatus as 'Status' from crm_vehicle where LoanID=@LoanID")
                   .Append(" UNION Select PropertyID as id,'Property' as 'SecurityType',ifnull(Address,'') as 'Security',PropertyValue as 'Value',ifnull(MortgageOwing,'0.0') as Owning , Owner as 'Owner',SecurityStatus as 'Status' from crm_property where LoanID=@LoanID")
                   .Append(" UNION Select BusinessID as id,'Company' as 'SecurityType',ifnull(CompanySecurity,'') as 'Security',CAST(('0.00') as DECIMAL(10,2)) as 'Value',CAST(('0.00') as DECIMAL(10,2)) as 'Owning' , CompanyName as 'Owner',SecurityStatus as 'Status' from crm_business where LoanID=@LoanID ");
              
 


                //sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                ds = DBhelper.ExecuteDataset(sbSql.ToString(), para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static int GetTotalSecurity(int LoanID)
        {
            int count = 0;
            try
            {
                var para = new[]{
                    DBhelper.SetParameter("@LoanID",LoanID)
                };
                String query = "select sum(u.total) as totalSecurity from (select count(*) as total from crm_property where LoanID=@LoanID UNION ALL select count(*) as total from crm_vehicle where LoanID=@LoanID UNION ALL select count(*) as total from  crm_business where LoanID=@LoanID) as u";
                DataSet ds = DBhelper.ExecuteDataset(query, para);
                count = Convert.ToInt32(ds.Tables[0].Rows[0]["totalSecurity"].ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return count;
        }

        public static DataSet GetVehicleSecurityById(int VehicleSecurityID)
        {
            DataSet ds;
            try
            {
                var para = new[]{
                    DBhelper.SetParameter("@VehicleSecurityID",VehicleSecurityID)
                };
                string sbsql = "Select * from crm_vehicle where VehicleID=@VehicleSecurityID";
                ds = DBhelper.ExecuteDataset(sbsql, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static DataSet GetCompanySecurityById(int CompanySecurityID)
        {
            DataSet ds;
            try
            {
                var para = new[]{
                    DBhelper.SetParameter("@CompanySecurityID",CompanySecurityID)
                };
                string sbsql = "Select * from crm_business where BusinessID=@CompanySecurityID";
                ds = DBhelper.ExecuteDataset(sbsql, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static DataSet GetPropertySecurityById(int PropertySecurityID)
        {
            DataSet ds;
            try
            {
                var para = new[]{
                    DBhelper.SetParameter("@PropertySecurityID",PropertySecurityID)
                };
                 string sbsql = "Select * from crm_property where PropertyID=@PropertySecurityID";
                ds = DBhelper.ExecuteDataset(sbsql, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static bool DeleteVehicleSecurity(int securityid)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@P_SecurityVehicleID",securityid)
                };
                string sbSql = "delete from crm_vehicle where VehicleID=@P_SecurityVehicleID";
                DBhelper.ExecuteNonQuery(sbSql, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public static bool DeleteCompanySecurity(int securityid)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@P_SecurityCompanyID",securityid)
                };
                string sbSql = "delete from crm_business where BusinessID=@P_SecurityCompanyID";
                DBhelper.ExecuteNonQuery(sbSql, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public static bool UpdateVehicleComment(int Vehicleid, string VehicleComment)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@P_SecurityVehicleID",Vehicleid),
                    DBhelper.SetParameter("@P_VehicleComment",VehicleComment)
                };
                string sbSql = "Update crm_vehicle  SET VehicleComment=@P_VehicleComment where VehicleID=@P_SecurityVehicleID";
                DBhelper.ExecuteNonQuery(sbSql, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public static bool UpdateCompanyComment(int Companyid, string CompanyComment)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@P_SecurityCompanyID",Companyid),
                    DBhelper.SetParameter("@P_CompanyComment",CompanyComment)
                };
                string sbSql = "Update crm_business  SET CompanyComment=@P_CompanyComment  where BusinessID=@P_SecurityCompanyID";
                DBhelper.ExecuteNonQuery(sbSql, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public static bool UpdatePropertyComment(int Propertyid, string PropertyComment)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@P_SecurityPropertyID",Propertyid),
                    DBhelper.SetParameter("@P_PropertyComment",PropertyComment)
                };
                string sbSql = "Update crm_property  SET PropertyComment=@P_PropertyComment where PropertyID=@P_SecurityPropertyID";
                DBhelper.ExecuteNonQuery(sbSql, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public static bool DeletePropertySecurity(int securityid)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@P_SecurityPropertyID",securityid)
                };
                string sbSql = "delete from crm_property where PropertyID=@P_SecurityPropertyID";
                DBhelper.ExecuteNonQuery(sbSql, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public static bool saveVehicleSecurity(int AppID, int SecurityVehicleID, string VIN, string EngineNo, string VehicleRegNo, string VehicleColour, string VehicleBadge, string VehicleModel, string VehicleMake, int VehicleYear, string RegisteredOwner, string VehicleSecurityStatus, string VehicleValue, string VehicleOwning, string VehicleValueSource, string VehicleOwningSource, DateTime ExpiryDate, string InsuranceType, string VehicleComment, int LoanID)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                     DBhelper.SetParameter("@P_AppID",AppID),
                     DBhelper.SetParameter("@P_SecurityVehicleID",SecurityVehicleID),
                     DBhelper.SetParameter("@P_VIN",VIN),
                     DBhelper.SetParameter("@P_EngineNo",EngineNo),
                     DBhelper.SetParameter("@P_VehicleRegNo",VehicleRegNo),
                     DBhelper.SetParameter("@P_VehicleColour",VehicleColour),
                     DBhelper.SetParameter("@P_VehicleBadge",VehicleBadge),
                     DBhelper.SetParameter("@P_VehicleModel",VehicleModel),
                     DBhelper.SetParameter("@P_VehicleMake",VehicleMake),
                     DBhelper.SetParameter("@P_VehicleYear",VehicleYear),
                     DBhelper.SetParameter("@P_RegisteredOwner",RegisteredOwner),
                     DBhelper.SetParameter("@P_VehicleSecurityStatus",VehicleSecurityStatus),
                     DBhelper.SetParameter("@P_VehicleValue" ,VehicleValue),
                     DBhelper.SetParameter("@P_VehicleOwning",VehicleOwning),
                     DBhelper.SetParameter("@P_VehicleValueSource" ,VehicleValueSource),
                     DBhelper.SetParameter("@P_VehicleOwningSource",VehicleOwningSource),
                     DBhelper.SetParameter("@P_ExpiryDate" ,ExpiryDate),
                     DBhelper.SetParameter("@P_InsuranceType" ,InsuranceType),
                     DBhelper.SetParameter("@P_VehicleComment", VehicleComment),
                     DBhelper.SetParameter("@P_LoanID", LoanID),
                     DBhelper.SetParameter("@P_CurrentContact_id", "0"),
                     DBhelper.SetParameter("@P_OriginalContact_id", "0")
                };

                DBhelper.ExecuteNonQuerySP("sp_saveVehicleSecurity", para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public static bool savePropertySecurity(int SecurityPropertyID, string Address, string TitleRef, string PropertyType, string PropertyOwner, string PropertySecurityStatus, string InterestType, DateTime LodgmentDate, DateTime CompletionDate, decimal PropertyValue, decimal PropertyOwning, string PropertyValueSource, string PropertyOwningSource, string Mortgagee, string SubSequentParties, string PropertyComment, int LoanID, int AppID)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                     DBhelper.SetParameter("@P_AppID",AppID),
                     DBhelper.SetParameter("@P_SecurityPropertyID",SecurityPropertyID),
                     DBhelper.SetParameter("@P_Address",Address ),
                     DBhelper.SetParameter("@P_TitleRef", TitleRef),
                     DBhelper.SetParameter("@P_PropertyType", PropertyType),
                     DBhelper.SetParameter("@P_PropertyOwner",PropertyOwner),
                     DBhelper.SetParameter("@P_PropertySecurityStatus",PropertySecurityStatus),
                     DBhelper.SetParameter("@P_InterestType",InterestType),
                     DBhelper.SetParameter("@P_LodgmentDate",LodgmentDate),
                     DBhelper.SetParameter("@P_CompletionDate",CompletionDate),
                     DBhelper.SetParameter("@P_PropertyValue" ,PropertyValue),
                     DBhelper.SetParameter("@P_PropertyOwning",PropertyOwning),
                     DBhelper.SetParameter("@P_PropertyValueSource" ,PropertyValueSource),
                     DBhelper.SetParameter("@P_PropertyOwningSource",PropertyOwningSource),
                     DBhelper.SetParameter("@P_Mortgagee" ,Mortgagee),
                     DBhelper.SetParameter("@P_SubSequentParties" ,SubSequentParties),
                     DBhelper.SetParameter("@P_PropertyComment", PropertyComment),
                     DBhelper.SetParameter("@P_LoanID", LoanID),
                     DBhelper.SetParameter("@P_CurrentContact_id", "0"),
                     DBhelper.SetParameter("@P_OriginalContact_id", "0")
                };

                DBhelper.ExecuteNonQuerySP("sp_savePropertySecurity", para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public static bool saveCompanySecurity(int AppID,int SecurityCompanyID, string Company, string Security, string SecurityStatus, DateTime RegistrationDate, decimal PriorChargee, decimal SubsequentChargee, string Comment, int LoanID)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                     DBhelper.SetParameter("@P_AppID",AppID),
                     DBhelper.SetParameter("@P_SecurityCompanyID",SecurityCompanyID),
                     DBhelper.SetParameter("@P_Company",Company ),
                     DBhelper.SetParameter("@P_Security", Security),
                     DBhelper.SetParameter("@P_SecurityStatus", SecurityStatus),
                     DBhelper.SetParameter("@P_RegistrationDate",RegistrationDate),
                     DBhelper.SetParameter("@P_PriorChargee",PriorChargee),
                     DBhelper.SetParameter("@P_SubsequentChargee",SubsequentChargee),
                     DBhelper.SetParameter("@P_Comment",Comment),
                     DBhelper.SetParameter("@P_LoanID", LoanID),
                     DBhelper.SetParameter("@P_CurrentContact_id", "0"),
                     DBhelper.SetParameter("@P_OriginalContact_id", "0")
                };

                DBhelper.ExecuteNonQuerySP("sp_saveCompanySecurity", para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
    }
}
